;;save lots button on main window
(defun insertdata_lots ()
  ;; -------------------------------
  ;; see save_template.lisp for details (very similar)
  (if (string-equal (text *ENTRY_MAT1*) "") 
      (dotimes (n 1) (setf (text *status_label*)
			   (format nil "...Can't save - No data in first row for materials."))
	(user-info-report "No data in first row of Materials." 4223421)
	(return-from insertdata_lots))
      )
  (if (string-equal (text *top_e_formula*) "")
      (dotimes (n 1) (setf (text *status_label*)
			   (format nil "...Can't save - No formula name entered."))
	(user-info-report "No formula name entered." 4223422)
	(return-from insertdata_lots))
      )
  (if (string-equal (text *top_e_lot*) "")
      (dotimes (n 1) (setf (text *status_label*)
			   (format nil "...Can't save - No lot number entered."))
	(user-info-report "No Lot# entered." 4223422)
	(return-from insertdata_lots))
      )
  (setf *existing_formula* "T") ;;initialize
  ;;check if lot/formula already exists, prompt user for confirmation
  (if (sqlite:execute-non-query *db* "select Lotnumber from lots_base where Lotnumber = ?" (text *top_e_lot*));;returns T if found, NIL if not
      (setf *existing_formula* (format nil "~a" (ask-yesno "This lot# already exists, are you sure you want to overwrite?"))))
  (if (string-equal *existing_formula* "NIL") ;; must be in its own if loop
      (return-from insertdata_lots)      
      )
  
  ;;set unix timestamp 
  (setf *dateforsql* (get-unix-time))
  ;;look for existing customer
  (if (not (sqlite:execute-non-query *db* "select Customer from lots_customers where Customer = ?" (text *top_e_customer*)))
      
      ;; insert new customer
      (sqlite:execute-non-query *db* "insert into lots_customers (Customer) values (?)" (text *top_e_customer*))
      ;;else, already found
      ;;(print "customer already found")
      )
  ;;get customerid
  (setf *customerid* (sqlite:execute-single *db* "select CustomerID from lots_customers where Customer = ?" (text *top_e_customer*)))
  ;;insert into lots_base
  (sqlite:execute-non-query *db* "insert into lots_base (Formula, Quantity, CustomerID, Timedateunix, Lotnumber, Notes) values (?, ?, ?, ?, ?, ?)" (text *top_e_formula*) (text *top_e_quantity*) *customerid* *dateforsql* (text *top_e_lot*) (text *top_e_notes*))

  (block blk_template
    
    ;;Good way to test: printf these in the printout function, assigned to any button.
    (loop for x from 1 to 6 do
      (setf *MAT_LOOP_MAT*
	    (symbol-in-symbol *MAT_LOOP_MAT* "*ENTRY_MAT" (write-to-string x) "*"))
      (setf *MAT_LOOP_LBS*
	    (symbol-in-symbol *MAT_LOOP_LBS* "*ENTRY_LBS" (write-to-string x) "*"))
      (setf *MAT_LOOP_OZ*
	    (symbol-in-symbol *MAT_LOOP_OZ*  "*ENTRY_OZ"  (write-to-string x) "*")) 
      (setf *MAT_LOOP_GMS*
	    (symbol-in-symbol *MAT_LOOP_GMS* "*ENTRY_GMS" (write-to-string x) "*"))
      (setf *MAT_LOOP_INT*
	    (symbol-in-symbol *MAT_LOOP_INT* "*ENTRY_INT" (write-to-string x) "*"))
      ;; dont care if there are blank rows. save everything.
      (sqlite:execute-non-query *db* "insert into lots_materials (Lbs, Oz, Grams, Materials, Internal, Lotnumber, Timedateunix) values (?, ?, ?, ?, ?, ?, ?)"
				(text (get-symbol-in-symbol *MAT_LOOP_LBS*))
				(text (get-symbol-in-symbol *MAT_LOOP_OZ*))
				(text (get-symbol-in-symbol *MAT_LOOP_GMS*))
				(text (get-symbol-in-symbol *MAT_LOOP_MAT*))
				(text (get-symbol-in-symbol *MAT_LOOP_INT*))
				(text *top_e_lot*) ;;each row entry will have lot and timestamp
				*dateforsql*)
	  ))

  ;; delete any old data:
  ;;
  ;; using new timedateunix stamp *dateforsql* to find old entry
  ;; store in variable the old timestamp:
  ;; (select Timedateunix from Templates_base where Formula = "" and Timedateunix != newstamp;)
  ;; drop rows for both tables that are old timestamp
  ;; e.g. (delete from Templates_base      where Timedateunix = timestamptodelete;)
  ;; e.g. (delete from Templates_materials where Timedateunix = timestamptodelete;)
  (setf *oldtimestamp* (sqlite:execute-single *db* "select Timedateunix from lots_base where Lotnumber = ? and Timedateunix != ?;" (text *top_e_lot*) *dateforsql*))
  (sqlite:execute-non-query *db* "delete from lots_base where Timedateunix = ? ;" *oldtimestamp*)
  (sqlite:execute-non-query *db* "delete from lots_materials where Timedateunix = ? ;" *oldtimestamp*)

  ;; Note: I decided to go with this approach, vs. having the lotnumber be the primary key. This is more resilient to data loss.
  ;; see db creation script notes.

  ;; message user
  (setf (text *status_label*)
	(format nil "...The lot has been saved to the db."))
  (user-info-report "Lot Saved." 4023333)
  )
